﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

using AbstractSql;

using MbUnit.Framework;

namespace UnitTests {
    [TestsOn(typeof(AbsDBCommand))]
    [TestFixture]
    public class AbsDBCommandTests {
        public AbsDBCommandTests() {
            AbsDBConnection.SetDefaultConnectionString("Test");
        }
        #region ExecuteScalar, single result returned
        [Test]
        public void WhenRowReturned_can_read_single_int() {
            int value = AbsDBCommand
                .SqlCommand("select ColInt from test where ColInt =1")
                .ExecuteScalar<int>();
            Assert.AreEqual(1, value);
        }
        [Test]
        public void WhenRowReturned_can_read_single_String() {
            string value = AbsDBCommand
                .SqlCommand("select ColString from test where ColInt =1")
                .ExecuteScalar<string>();
            Assert.AreEqual("test", value);
        }
        [Test]
        public void WhenRowReturned_can_read_single_String_empty() {
            string value = AbsDBCommand
                .SqlCommand("select ColString from test where ColInt =2")
                .ExecuteScalar<string>();
            Assert.AreEqual(string.Empty, value);
        }
        [Test]
        public void WhenRowReturned_can_read_single_Long() {
            long value = AbsDBCommand
                .SqlCommand("select ColBigInt from test where ColInt =1")
                .ExecuteScalar<long>();
            Assert.AreEqual(1, value);
        }
        [Test]
        public void WhenRowReturned_can_read_single_Date() {
            DateTime value = AbsDBCommand
                .SqlCommand("select ColDate from test where ColInt =1")
                .ExecuteScalar<DateTime>();
            Assert.AreEqual(new DateTime(2010, 1, 1), value);
        }
        #endregion
        #region ExecuteScalar, no rows returned, read nullable values
        [Test]
        public void WhenNoRowReturned_can_read_single_nullable_int() {
            int? value = AbsDBCommand
                .SqlCommand("select ColInt from test where 1=0")
                .ExecuteScalar<int?>();
            Assert.IsNull(value);
        }
        [Test]
        public void WhenNoRowReturned_can_read_single_nullable_string() {
            string value = AbsDBCommand
                .SqlCommand("select ColString from test where 1=0")
                .ExecuteScalar<string>();
            Assert.IsNull(value);
        }
        [Test]
        public void WhenNoRowReturned_can_read_single_nullable_DateTime() {
            DateTime? value = AbsDBCommand
                .SqlCommand("select ColDate from test where 1=0")
                .ExecuteScalar<DateTime?>();
            Assert.IsNull(value);
        }
        #endregion
        #region ExecuteScalar, no rows returned, read default values
        [Test]
        public void WhenNoRowReturned_CanRead_DefaultInt() {
            int value = AbsDBCommand
                .SqlCommand("select ColInt from test where 1=0")
                .ExecuteScalar<int>(-1);
            Assert.AreEqual(-1, value);
        }
        [Test]
        public void WhenNoRowReturned_CanRead_DefaultInt_NullableInt() {
            int? value = AbsDBCommand
                .SqlCommand("select ColInt from test where 1=0")
                .ExecuteScalar<int?>(-1);
            Assert.AreEqual(-1, value);
        }
        [Test]
        public void WhenNoRowReturned_CanRead_DefaultDateTime() {
            DateTime value = AbsDBCommand
                .SqlCommand("select ColDate from test where 1=0")
                .ExecuteScalar<DateTime>(new DateTime(2000,1,1));
            Assert.AreEqual(new DateTime(2000, 1, 1), value);
        }
        [Test]
        public void WhenNoRowReturned_CanRead_DefaultDateTime_NullableDateTime() {
            DateTime? value = AbsDBCommand
                .SqlCommand("select ColDate from test where 1=0")
                .ExecuteScalar<DateTime?>(new DateTime(2000, 1, 1));
            Assert.AreEqual(new DateTime(2000, 1, 1), value);
        }
        [Test]
        public void WhenNoRowReturned_CanRead_DefaultString_String() {
            string value = AbsDBCommand
                .SqlCommand("select ColString from test where 1=0")
                .ExecuteScalar<string>("default");
            Assert.AreEqual("default", value);
        }
        #endregion
        #region ExecuteScalar, no rows returned, throws exception for non nullable types
        [Test]
        [ExpectedException(typeof(NullReferenceException))]
        public void WhenNoRowReturned_ReadingDateTime_ThrowsException() {
            DateTime value = AbsDBCommand
                .SqlCommand("select ColDate from test where 1=0")
                .ExecuteScalar<DateTime>();
        }
        [Test]
        [ExpectedException(typeof(NullReferenceException))]
        public void WhenNoRowReturned_ReadingInt_ThrowsException() {
            int value = AbsDBCommand
                .SqlCommand("select ColInt from test where 1=0")
                .ExecuteScalar<int>();
        }
        #endregion
        #region ExecuteScalar, value null returned, read nullable types
        [Test]
        public void WhenRowReturned_can_read_single_nullable_int() {
            int? value = AbsDBCommand
                .SqlCommand("select ColInt from test where ColInt is null")
                .ExecuteScalar<int?>();
            Assert.IsNull(value);
        }
        [Test]
        public void WhenRowReturned_can_read_single_nullable_DateTime() {
            DateTime? value = AbsDBCommand
                .SqlCommand("select ColDate from test where ColInt is null")
                .ExecuteScalar<DateTime?>();
            Assert.IsNull(value);
        }
        [Test]
        public void WhenRowReturned_can_read_single_nullable_String() {
            string value = AbsDBCommand
                .SqlCommand("select ColString from test where ColInt is null")
                .ExecuteScalar<string>();
            Assert.IsNull(value);
        }
        #endregion
        #region ExecuteScalar, value null returned, read default value
        [Test]
        public void WhenRowReturned_DBNull_ReturnsDefault_Int() {
            int value = AbsDBCommand
                .SqlCommand("select ColInt from test where ColInt is null")
                .ExecuteScalar<int>(-1);
            Assert.AreEqual(-1, value);
        }
        [Test]
        public void WhenRowReturned_DBNull_ReturnsDefault_NullableInt() {
            int? value = AbsDBCommand
                .SqlCommand("select ColInt from test where ColInt is null")
                .ExecuteScalar<int?>(-1);
            Assert.AreEqual(-1, value);
        }
        [Test]
        public void WhenRowReturned_DBNull_ReturnsDefault_DateTime() {
            DateTime value = AbsDBCommand
                .SqlCommand("select ColDate from test where ColInt is null")
                .ExecuteScalar<DateTime>(new DateTime(2000, 1, 1));
            Assert.AreEqual(new DateTime(2000, 1, 1), value);
        }
        [Test]
        public void WhenRowReturned_DBNull_ReturnsDefault_NullableDateTime() {
            DateTime? value = AbsDBCommand
                .SqlCommand("select ColDate from test where ColInt is null")
                .ExecuteScalar<DateTime?>(new DateTime(2000,1,1));
            Assert.AreEqual(new DateTime(2000, 1, 1), value);
        }
        [Test]
        public void WhenRowReturned_DBNull_ReturnsDefault_NullableString() {
            string value = AbsDBCommand
                .SqlCommand("select ColString from test where ColInt is null")
                .ExecuteScalar<string>("default");
            Assert.AreEqual("default", value);
        }
        #endregion
        #region Live Instance
        [Test]
        public void ExecuteScalar_CanUseLiveInstance() {
            string connString = ConfigurationManager.ConnectionStrings["Test"].ConnectionString;
            SqlConnection conn = new SqlConnection(connString);
            conn.Open();
            int value = AbsDBCommand
                .WithLiveConnection(conn)
                .SqlCommand("select ColInt from test where ColInt =1")
                .ExecuteScalar<int>();
            Assert.AreEqual(1, value);
            value = AbsDBCommand
                .WithLiveConnection(conn)
                .SqlCommand("select ColInt from test where ColInt =2")
                .ExecuteScalar<int>();
            Assert.AreEqual(2, value);
            Assert.AreEqual(ConnectionState.Open, conn.State);
            conn.Close();
        }
        [Test]
        public void ExecuteReader_CanUseLiveInstance() {
            string connString = ConfigurationManager.ConnectionStrings["Test"].ConnectionString;
            SqlConnection conn = new SqlConnection(connString);
            conn.Open();
            int temp=-1;
            AbsDBCommand
                .WithLiveConnection(conn)
                .SqlCommand("select ColInt from test where ColInt is not null order by ColInt")
                .ExecuteReader(delegate(IDataReader r) { temp = (int)r["ColInt"]; });
            Assert.AreEqual(3, temp);
            AbsDBCommand
                .WithLiveConnection(conn)
                .SqlCommand("select ColInt from test where ColInt is not null order by ColInt desc")
                .ExecuteReader(delegate(IDataReader r) { temp = (int)r["ColInt"]; });
            Assert.AreEqual(1, temp);
            Assert.AreEqual(ConnectionState.Open, conn.State);
            conn.Close();
        }
        [Ignore("not implemented")]
        [Test]
        public void ExecuteReader_CanUseLiveInstance_Nested() {
            string connString = ConfigurationManager.ConnectionStrings["Test"].ConnectionString;
            SqlConnection conn = new SqlConnection(connString);
            conn.Open();
            int temp = -1;
            AbsDBCommand
                .WithLiveConnection(conn)
                .SqlCommand("select ColInt from test where ColInt is not null order by ColInt")
                .ExecuteReader(delegate(IDataReader r) {
                    temp = (int)r["ColInt"];
                    AbsDBCommand
                    .WithLiveConnection(conn)
                    .SqlCommand("select ColInt from test where ColInt is not null order by ColInt desc")
                    .ExecuteReader(delegate(IDataReader ri) { temp = (int)ri["ColInt"]; });
                });
            Assert.AreEqual(3, temp);
            conn.Close();
        }
        [Test]
        public void ExecuteReaderSingle_CanUseLiveInstance() {
            string connString = ConfigurationManager.ConnectionStrings["Test"].ConnectionString;
            SqlConnection conn = new SqlConnection(connString);
            conn.Open();
            int temp = -1;
            AbsDBCommand
                .WithLiveConnection(conn)
                .SqlCommand("select ColInt from test where ColInt =1")
                .ExecuteReaderSingle(delegate(IDataReader r) { temp = (int)r["ColInt"]; });
            Assert.AreEqual(1, 1);
            AbsDBCommand
                .WithLiveConnection(conn)
                .SqlCommand("select ColInt from test where ColInt =2")
                .ExecuteReaderSingle(delegate(IDataReader r) { temp = (int)r["ColInt"]; });
            Assert.AreEqual(2, temp);
            Assert.AreEqual(ConnectionState.Open, conn.State);
            conn.Close();
        }
        #endregion
        #region ExecuteReaderSingle
        [Test]
        public void ExecuteReaderSingle_WhenNoRowReturned_ActionIsNotExecuted() {
            bool executed = false;
            AbsDBCommand
                .SqlCommand("select ColInt from test where 1=0")
                .ExecuteReaderSingle(delegate { executed = true; });
            Assert.AreEqual(false, executed);
        }
        [Test]
        public void ExecuteReaderSingle_WhenRowReturned_ActionIsExecuted() {
            bool executed = false;
            AbsDBCommand
                .SqlCommand("select ColInt from test where ColInt is null")
                .ExecuteReaderSingle(delegate { executed = true; });
            Assert.AreEqual(true, executed);
        }
        #endregion
        #region ExecuteReader
        [Test]
        public void Reader_WhenNoRowReturned_ActionIsNotExecuted() {
            bool executed = false;
            AbsDBCommand
                .SqlCommand("select ColInt from test where 1=0")
                .ExecuteReader(delegate { executed = true; });
            Assert.AreEqual(false, executed);
        }
        [Test]
        public void Reader_MultipleRowsReturned() {
            int count = 0;
            AbsDBCommand
                .SqlCommand("select ColInt from test")
                .ExecuteReader(delegate { count++; });
            Assert.IsTrue(count > 2);
        }
        [Test]
        public void Reader_MultipleRowsReturned_NonNull_CanReadValues() {
            int count = 0, value;
            AbsDBCommand
                .SqlCommand("select ColInt from test where ColInt is not null")
                .ExecuteReader(delegate(IDataReader r) { value = (int)r["ColInt"]; count += value; });
            Assert.IsTrue(count > 2);
        }
        [Test]
        public void Reader_MultipleRowsReturned_NullValues_CanReadValuesAsNullables() {
            int count = 0;
            int? value;
            AbsDBCommand
                .SqlCommand("select ColInt from test where ColInt is null")
                .ExecuteReader(delegate(IDataReader r) { value = (r["ColInt"] as int?); count += value??0; });
            Assert.AreEqual(0, count);
        }
        [Test]
        [ExpectedException(typeof(InvalidCastException))]
        public void Reader_MultipleRowsReturned_NullValues_ThrowsExceptionForNonNullableTypes() {
            int count = 0;
            int value;
            AbsDBCommand
                .SqlCommand("select ColInt from test where ColInt is null")
                .ExecuteReader(delegate(IDataReader r) { value = ((int)r["ColInt"]); count += value; });
            Assert.AreEqual(0, count);
        }
        #endregion
        #region DataSet
        [Test]
        public void AsDataTable_MultipleRowsReturned_CanReadValues() {
            DataTable dt = AbsDBCommand
                .SqlCommand("select ColInt from test where ColInt is not null order by ColInt").AsDataTable();
            int value = (int)dt.Rows[0].ItemArray[0];
            Assert.AreEqual(1, value);
            Assert.IsTrue(dt.Rows.Count>2);
        }
        #endregion
        #region ExecuteNonQuery
        [Test]
        public void ExecuteNonQuery_ValidateExecution() {
            int dummyValue = -1;
            AbsDBCommand
                .SqlCommand("insert into Test (ColInt) values(1000)")
                .ExecuteNonQuery();
            int result = dummyValue;
            AbsDBCommand
                .SqlCommand("select ColInt from Test where ColInt=1000")
                .ExecuteReader(delegate(IDataReader r) { result = (int)r["ColInt"]; });
            Assert.AreEqual(1000, result);
            AbsDBCommand
                .SqlCommand("delete from Test where ColInt =1000")
                .ExecuteNonQuery();
            int? result2 = dummyValue; //random value
            AbsDBCommand
                .SqlCommand("select ColInt from Test where ColInt=1000")
                .ExecuteReader(delegate(IDataReader r) { result2 = (int?)r["ColInt"]; }); //<== should not execute
            Assert.AreEqual(dummyValue, result2); //<== should be original value
        }
        #endregion
        #region AsList<T>
        [Test]
        public void AsList_EmptyResult_ReturnsEmptyList() {
            List<int> result = AbsDBCommand
                .SqlCommand("select ColInt from test where 1=0").AsList<int>();
            Assert.AreEqual(0, result.Count);
        }
        [Test]
        public void AsList_MultipleRowsReturned_CanReadInt() {
            List<int> result = AbsDBCommand
                .SqlCommand("select ColInt from test where ColInt is not null").AsList<int>();
            Assert.IsTrue(result.Count > 1);
        }
        [Test]
        public void AsList_MultipleRowsReturned_CanReadNullableInt() {
            List<int?> result = AbsDBCommand
                .SqlCommand("select ColInt from test").AsList<int?>();
            Assert.IsTrue(result.Count > 1);
        }
        [Test]
        public void AsList_MultipleRowsReturned_CanReadStrings() {
            List<string> result = AbsDBCommand
                .SqlCommand("select ColString from test").AsList<string>();
            Assert.IsTrue(result.Count > 1);
        }
        [Test]
        public void AsList_MultipleRowsReturned_CanReadDates() {
            List<DateTime> result = AbsDBCommand
                .SqlCommand("select ColDate from test where ColDate is not null").AsList<DateTime>();
            Assert.IsTrue(result.Count > 1);
        }
        [Test]
        public void AsList_MultipleRowsReturned_CanReadNullableDates() {
            List<DateTime?> result = AbsDBCommand
                .SqlCommand("select ColDate from test").AsList<DateTime?>();
            Assert.IsTrue(result.Count > 1);
        }
        #endregion
        #region AsList<T>(Mapper)
        [Test]
        public void AsListToMapper_EmptyResult_ReturnsEmptyList() {
            List<TestClass> result = AbsDBCommand
                .SqlCommand("select ColInt, ColString, ColDate from test where 1=0")
                .AsList<TestClass>(new TestMapper());
            Assert.AreEqual(0, result.Count);
        }
        [Test]
        public void AsListToMapper_MultipleRowsReturned_CanReadValues() {
            List<TestClass> result = AbsDBCommand
                .SqlCommand("select ColInt, ColString, ColDate from test")
                .AsList<TestClass>(new TestMapper());
            Assert.IsTrue(result.Count > 1);
        }
        #endregion
        #region AsHashTable
        [Test]
        public void AsHashTable_ReturnsFirstRow() {
            Hashtable result = AbsDBCommand
                .SqlCommand("select ColInt, ColString, ColDate from test where ColInt is not null order by ColInt")
                .AsHashTable();
            Assert.AreEqual(3, result.Count);
            Assert.AreEqual(1, result["ColInt"]);
        }
        [Test]
        public void AsHashTable_CanReadNulls() {
            Hashtable result = AbsDBCommand
                .SqlCommand("select ColInt, ColString, ColDate from test where ColInt is null")
                .AsHashTable();
            Assert.AreEqual(3, result.Count);
            Assert.AreEqual(null, result["ColInt"]);
            Assert.AreEqual(null, result["ColString"]);
            Assert.AreEqual(null, result["ColDate"]);
        }
        [Test]
        public void AsHashTable_EmptyResult_ReturnsEmptyHashTable() {
            Hashtable result = AbsDBCommand
                .SqlCommand("select ColInt, ColString, ColDate from test where 1=0")
                .AsHashTable();
            Assert.AreEqual(0, result.Count);
        }
        #endregion
        public void Test() {
            //object oInt = null;
            //int? i = (int?)oInt;
            int? i = ExecuteScalar<int?>(-1);
            Assert.AreEqual(null, i);
        }
        public T ExecuteScalar<T>(T defaultValue) {
            object o = null;
            T result = default(T);
            result = (T)o;
            return result;
        }
    }
    class TestClass {
        public int? TestInt { get; set; }
        public string TestString { get; set; }
        public DateTime? TestDate { get; set; }
    }
    class TestMapper : ModelMapper<TestClass> {
        public override TestClass Map(IDataReader reader) {
            Reader r = new Reader(reader);
            TestClass result = new TestClass();
            result.TestInt = r.Get<int?>("ColInt");
            result.TestString = r.Get<string>("ColString");
            result.TestDate = r.Get<DateTime?>("ColDate");
            return result;
        }
    }
}